/**
 * Copyright (C)  2011  chen wei hua.
 * Permission is granted to copy, distribute and/or modify this document
 * under the terms of the GNU Free Documentation License, Version 2.0
 * or any later version published by the Free Software Foundation;
 * with no Invariant Sections, no Front-Cover Texts, and no Back-Cover Texts.
 * A copy of the license is included in the section entitled "GNU Free Documentation License".
 */
package com.javaeye.accounting.dao;

import java.sql.SQLException;
import java.util.List;

import org.hibernate.Criteria;
import org.hibernate.FetchMode;
import org.hibernate.Query;
import org.hibernate.HibernateException;
import org.hibernate.Session;
import org.hibernate.criterion.Order;
import org.hibernate.criterion.Restrictions;
import org.springframework.orm.hibernate3.HibernateCallback;
import org.springframework.orm.hibernate3.support.HibernateDaoSupport;

import com.javaeye.accounting.dto.MaterialsMonthBatchDetail;

public class MaterialsMonthBatchDetailDAOHibernate extends HibernateDaoSupport implements MaterialsMonthBatchDetailDAO {
	
	@Override
	public void saveMaterialsMonthBatchDetail(MaterialsMonthBatchDetail detail) {
		getHibernateTemplate().saveOrUpdate(detail);
	}
	
	/**
	 * 取得对应月度的物料库存月报信息
	 * @param monthNo
	 * @param categoryId
	 * @return
	 */
	@SuppressWarnings("unchecked")
	public List<MaterialsMonthBatchDetail> getMonthInfoByChildCategory(final String monthNo, final int categoryId) {
		HibernateCallback action = new HibernateCallback() {
			@Override
			public Object doInHibernate(Session session)
					throws HibernateException, SQLException {
				Criteria crit = session.createCriteria(MaterialsMonthBatchDetail.class);
				//这里为了去除重复记录
				crit.setResultTransformer(Criteria.DISTINCT_ROOT_ENTITY)
				.add(Restrictions.eq("monthNo", monthNo))
				.setFetchMode("skuDetail", FetchMode.JOIN)
				.addOrder(Order.asc("skuDetail.id"))
				.addOrder(Order.asc("batchNo"));
				
				crit.createAlias("skuDetail.materials", "d")
				.add(Restrictions.eq("d.category.id", categoryId))
				;
				return crit.list();
			}
		};
		return getHibernateTemplate().executeFind(action);
	}
	
	/**
	 * 取得对应月度的物料库存月报信息
	 * @param monthNo
	 * @param categoryId
	 * @return
	 */
	@SuppressWarnings("unchecked")
	public List<MaterialsMonthBatchDetail> getMonthInfo(final String monthNo, final int categoryId, final String queryType) {
		HibernateCallback action = new HibernateCallback() {
			@Override
			public Object doInHibernate(Session session)
					throws HibernateException, SQLException {
				Criteria crit = session.createCriteria(MaterialsMonthBatchDetail.class);
				//这里为了去除重复记录
				crit.setResultTransformer(Criteria.DISTINCT_ROOT_ENTITY)
				.add(Restrictions.eq("monthNo", monthNo))
				.setFetchMode("skuDetail", FetchMode.JOIN)
				.addOrder(Order.asc("skuDetail.id"))
				.addOrder(Order.asc("batchNo"));
				
				Criteria materialsCrit = crit.createCriteria("skuDetail.materials")
				.add(Restrictions.eq("rootCategory.id", categoryId))
				;
				if ("".equals(queryType) || queryType == null) {
					
				} else {
					materialsCrit.add(Restrictions.like("type", "%" + queryType + "%"));
				}
				return crit.list();
			}
		};
		return getHibernateTemplate().executeFind(action);
	}
	
	/**
	 * 取得对应月度的物料库存月报信息
	 * @param monthNo
	 * @param categoryId
	 * @return
	 */
	@SuppressWarnings("unchecked")
	public List<MaterialsMonthBatchDetail> getMonthInfo(String monthNo, int skuId) {
		final String queryString = "from MaterialsMonthBatchDetail d where d.monthNo=? and d.skuDetail.id=?";
		List result = getHibernateTemplate().find(queryString, new Object[]{monthNo, skuId});
		return result;
	}

	
	/**
	 * 取得对应月度的物料库存月报信息
	 * @param materialsId
	 * @param monthNo
	 * @return
	 */
	@SuppressWarnings("unchecked")
	public long getMonthInfoCount(String monthNo) {
		final String queryString = "select count(*) from MaterialsMonthBatchDetail d where d.monthNo=?";
		List result = getHibernateTemplate().find(queryString, monthNo);
		if (result == null || result.size() == 0) {
			return 0;
		}
		return (Long)result.get(0);
	}
	
	/**
	 * 取得对应月度和批次的物料库存信息
	 * @param materialsId
	 * @param monthNo
	 * @return
	 */
	@SuppressWarnings("unchecked")
	@Override
	public MaterialsMonthBatchDetail getMaterialsMonthBatchDetail(int materialsId, String batchNo, String monthNo) {
		final String queryString = "from MaterialsMonthBatchDetail d where d.skuDetail.id=? and d.batchNo=? and d.monthNo=?";
		List result = getHibernateTemplate().find(
				queryString, new Object[] {materialsId, batchNo, monthNo});
		if (result == null || result.size() == 0) {
			return null;
		}
		return (MaterialsMonthBatchDetail)result.get(0);
	}
	
	/**
	 * 取得对应月度和批次的物料库存信息
	 * @param materialsId
	 * @param monthNo
	 * @return
	 */
	@Override
	public MaterialsMonthBatchDetail getMaterialsMonthBatchDetail(int detailId) {
		return (MaterialsMonthBatchDetail)getHibernateTemplate().get(
				MaterialsMonthBatchDetail.class, detailId);
	}
	
	/**
	 * 取得对应月度和批次的物料库存信息
	 * @param materialsId
	 * @param monthNo
	 * @return
	 */
	@SuppressWarnings("unchecked")
	@Override
	public List<MaterialsMonthBatchDetail> getMaterialsMonthBatchDetail(final int materialsId, final String monthNo) {
		HibernateCallback action = new HibernateCallback() {
			@Override
			public Object doInHibernate(Session session)
					throws HibernateException, SQLException {
				Criteria crit = session.createCriteria(MaterialsMonthBatchDetail.class);
				//这里为了去除重复记录
				crit.setResultTransformer(Criteria.DISTINCT_ROOT_ENTITY)
				.add(Restrictions.eq("monthNo", monthNo))
				.setFetchMode("skuDetail", FetchMode.JOIN)
				.addOrder(Order.asc("skuDetail.id"))
				.addOrder(Order.asc("batchNo"))
				
				.createCriteria("skuDetail.materials")
				.add(Restrictions.eq("id", materialsId));

				return crit.list();
			}
		};
		return getHibernateTemplate().executeFind(action);
	}
	
	/**
	 * 取得对应月度的物料数量>0的所有批次
	 * @param materialsId
	 * @param monthNo
	 * @return
	 */
	@SuppressWarnings("unchecked")
	public List<String> getBatchNos(int materialsId, String monthNo) {
		final String queryString = 
			"select d.batchNo from MaterialsMonthBatchDetail d " +
			"where d.skuDetail.id=? and d.monthNo=? " +
			"and (d.number > 0 or d.defectiveNumber > 0)";
		List result = getHibernateTemplate().find(
				queryString, new Object[] {materialsId, monthNo});
		return result;
	}
	
	/**
	 * 取得对应月度的物料数量>0的物料ID
	 * @param materialsId
	 * @param monthNo
	 * @return
	 */
	@SuppressWarnings("unchecked")
	public List<Integer> getMaterialsIds(String monthNo) {
		final String queryString = 
			"select distinct d.skuDetail.id from MaterialsMonthBatchDetail d " +
			"where d.monthNo=? " +
			"and (d.number > 0 or d.defectiveNumber > 0)";
		List result = getHibernateTemplate().find(queryString, monthNo);
		return result;
	}
	
	public void removeDetail(final int materialsId) {
		HibernateCallback action = new HibernateCallback() {
			@Override
			public Object doInHibernate(Session session)
					throws HibernateException, SQLException {
				final String queryString = "delete from MaterialsMonthBatchDetail d where d.skuDetail.id=" + materialsId;
				Query query=session.createQuery(queryString);
				int size = query.executeUpdate();
				return size;
			}
		};
		getHibernateTemplate().execute(action);
	}
}
